/**
 * 
 */
package com.feib.stms.dao.jdbc;


import java.sql.Types;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.lang.StringUtils;
import org.springframework.dao.support.DataAccessUtils;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.simple.SimpleJdbcInsert;
import org.springframework.jdbc.support.incrementer.OracleSequenceMaxValueIncrementer;
import org.springframework.stereotype.Repository;

import com.feib.stms.dao.TempUserDao;
import com.feib.stms.dao.jdbc.rowmapper.TempUserRowMapper;
import com.feib.stms.model.TempUser;
import com.feib.stms.pagination.PaginatedList;
import com.feib.stms.util.UserUtil;

/**
 * @author Lion Gao
 *
 */
@Repository("userTempDao")
public class TempUserJdbcDao extends BaseJdbcDao implements TempUserDao 
{
	private OracleSequenceMaxValueIncrementer sequence;
	
	private SimpleJdbcInsert userJdbcInsert;
	
	@Override
	protected void initDao() throws Exception {
		super.initDao();
		this.sequence = new  OracleSequenceMaxValueIncrementer(this.getDataSource(), "TEMP_USER_PK_SEQ");
		
		this.userJdbcInsert = new  SimpleJdbcInsert(this.getDataSource());
		this.userJdbcInsert.withTableName("TEMP_USER")
			.usingColumns("TEMP_USER_PK", "GROUP_PK", "SECURITIES_PK" ,"USER_ID" ,"USER_PWD" ,"USER_NAME" 
					,"USER_PHONE_NO" ,"USER_EMAIL", "USER_NOTE", "CERTIFICATE", "DEAL_SECURITIES_NOS" 
					,"IS_LOCKED" ,"IS_ENABLED" ,"PWD_EXPIRED_DATE" ,"LAST_LOGIN_DATE" ,"PWD_HISTORY" 
					,"PWD_ERROR_TIME" ,"CAPTCHA_ERROR_TIME" ,"RECORD_STATUS" ,"CONFIRM_USER_PK" 
					,"CONFIRM_STATUS" ,"CONFIRM_DATE" ,"CREATED_BY" ,"CREATED_DATE");
		
	}

	/*
	 * (non-Javadoc)
	 * @see com.feib.stms.dao.UserTempDao#getByUserTempPk(java.lang.Long)
	 */
	public TempUser getByTempUserPk(Long userPk)
	{
		StringBuffer sql = new StringBuffer();
		MapSqlParameterSource parameters = new MapSqlParameterSource();
		sql.append("SELECT U.*");
		sql.append(" FROM TEMP_USER U");
		sql.append(" WHERE 1 = 1 ");
		sql.append(" AND U.TEMP_USER_PK = :TEMP_USER_PK");
		sql.append(" AND U.CONFIRM_STATUS IS NULL");//放行狀態 null: 待放行 Y: 放行 N: 退回
		parameters.addValue("TEMP_USER_PK",  userPk, Types.NUMERIC);
		
		return DataAccessUtils.singleResult(this.getSimpleJdbcTemplate().query(sql.toString(), new TempUserRowMapper(), parameters));
	}

	/*
	 * (non-Javadoc)
	 * @see com.feib.stms.dao.UserTempDao#insertTempUser(com.feib.stms.model.TempUser)
	 */
	public int insertTempUser(TempUser user) {
		MapSqlParameterSource parameters = new MapSqlParameterSource();
		Long tempUserPk = new Long(this.sequence.nextLongValue());
		parameters.addValue("TEMP_USER_PK", tempUserPk, Types.NUMERIC);
		parameters.addValue("GROUP_PK",  user.getGroup().getGroupPk(), Types.NUMERIC);
		parameters.addValue("SECURITIES_PK",  user.getSecurities()==null?user.getSecurities():user.getSecurities().getSecuritiesPk(), Types.NUMERIC);
		parameters.addValue("USER_ID", user.getUserId(), Types.VARCHAR);
		parameters.addValue("USER_PWD",  user.getUserPwd(), Types.VARCHAR);
		parameters.addValue("USER_NAME",  user.getUserName(), Types.VARCHAR);
		parameters.addValue("USER_PHONE_NO",  user.getUserPhoneNo(), Types.VARCHAR);
		parameters.addValue("USER_EMAIL",  user.getUserEmail(), Types.VARCHAR);
		parameters.addValue("USER_NOTE",  user.getUserNote(), Types.VARCHAR);
		parameters.addValue("CERTIFICATE",  user.getCertificateKey(), Types.VARCHAR);
		parameters.addValue("DEAL_SECURITIES_NOS",  user.getDealSecuritiesNos(), Types.VARCHAR);
		parameters.addValue("IS_LOCKED",  convertToInteger(user.getLocked()), Types.NUMERIC);
		parameters.addValue("IS_ENABLED",  convertToInteger(user.getEnabled()), Types.NUMERIC);
		parameters.addValue("PWD_EXPIRED_DATE",  user.getPwdExpiredDate(), Types.TIMESTAMP);
		parameters.addValue("LAST_LOGIN_DATE",  user.getLastLogingDate(), Types.TIMESTAMP);
		parameters.addValue("PWD_HISTORY",  user.getPwdHistory(), Types.VARCHAR);
		parameters.addValue("PWD_ERROR_TIME",  user.getPwdErrorTime(), Types.NUMERIC);
		parameters.addValue("CAPTCHA_ERROR_TIME",  user.getCaptchaErrorTime(), Types.NUMERIC);
		parameters.addValue("RECORD_STATUS",  user.getRecordStatus(), Types.VARCHAR);
		parameters.addValue("CONFIRM_USER_PK",  user.getConfirmUserPk(), Types.NUMERIC);
		parameters.addValue("CONFIRM_STATUS",  user.getConfirmStatus(), Types.VARCHAR);
		parameters.addValue("CONFIRM_DATE",  user.getConfirmDate(), Types.TIMESTAMP);
		parameters.addValue("CREATED_BY",  user.getCreatedBy(), Types.VARCHAR);
		parameters.addValue("CREATED_DATE",  user.getCreatedDate(), Types.TIMESTAMP);
				
		int insertCounts = this.userJdbcInsert.execute(parameters);
		if (insertCounts > 0)
			user.setTempUserPk(tempUserPk);
		return insertCounts;
	}
	
	/*
	 * (non-Javadoc)
	 * @see com.feib.stms.dao.TempUserDao#countTempUserId(java.lang.String, java.lang.Long)
	 */
	public int countTempUserId(String userId,Long groupPk) {
		/* 查詢 SQL */
		StringBuffer sql = new StringBuffer();
		/* 查詢條件 */
		MapSqlParameterSource parameters = new MapSqlParameterSource();
		sql.append("SELECT  COUNT(*)  FROM TEMP_USER WHERE USER_ID = :USER_ID AND GROUP_PK = :GROUP_PK");
		parameters.addValue("USER_ID",  userId, Types.VARCHAR);
		parameters.addValue("GROUP_PK",  groupPk, Types.NUMERIC);
		int groupCount = this.getSimpleJdbcTemplate().queryForInt(sql.toString(), parameters);
		return groupCount;
	}
	
	/*
	 * (non-Javadoc)
	 * @see com.feib.stms.dao.TempUserDao#countTempUserId(java.lang.Long, java.lang.String, java.lang.Long)
	 */
	public int countTempUserId(Long userPk, String userId,Long groupPk) {
		/* 查詢 SQL */
		StringBuffer sql = new StringBuffer();
		/* 查詢條件 */
		MapSqlParameterSource parameters = new MapSqlParameterSource();
		sql.append("SELECT  COUNT(*)  FROM TEMP_USER WHERE USER_ID = :USER_ID AND TEMP_USER_PK != :TEMP_USER_PK");
		sql.append(" AND GROUP_PK = :GROUP_PK");
		parameters.addValue("USER_ID",  userId, Types.VARCHAR);
		parameters.addValue("TEMP_USER_PK", userPk, Types.NUMERIC);
		parameters.addValue("GROUP_PK",  groupPk, Types.NUMERIC);
		int groupCount = this.getSimpleJdbcTemplate().queryForInt(sql.toString(), parameters);
		return groupCount;
	}
	
	/*
	 * (non-Javadoc)
	 * @see com.feib.stms.dao.TempUserDao#updateTempUser(com.feib.stms.model.TempUser)
	 */
	public int updateTempUser(TempUser user) {
		StringBuffer sql = new StringBuffer();
		MapSqlParameterSource parameters = new MapSqlParameterSource();
		sql.append("UPDATE TEMP_USER SET ");
		sql.append(" SECURITIES_PK = :SECURITIES_PK ");
		sql.append(" , USER_NAME = :USER_NAME");
		sql.append(" , USER_PHONE_NO = :USER_PHONE_NO");
		sql.append(" , USER_EMAIL = :USER_EMAIL");
		sql.append(" , IS_ENABLED = :IS_ENABLED");
		sql.append(" , IS_LOCKED = :IS_LOCKED");
		sql.append(" , CERTIFICATE = :CERTIFICATE");
		sql.append(" , USER_NOTE = :USER_NOTE");
		sql.append(" , DEAL_SECURITIES_NOS = :DEAL_SECURITIES_NOS");
		sql.append(" , UPDATE_BY = :UPDATE_BY");
		sql.append(" , UPDATE_DATE = :UPDATE_DATE");
		sql.append(" , CONFIRM_USER_PK = :CONFIRM_USER_PK");
		sql.append(" , CONFIRM_STATUS = :CONFIRM_STATUS");
		sql.append(" , CONFIRM_DATE = :CONFIRM_DATE");
		sql.append(" WHERE TEMP_USER_PK = :TEMP_USER_PK");
		sql.append(" AND GROUP_PK = :GROUP_PK");
		
		parameters.addValue("SECURITIES_PK", null == user.getSecurities() ? null : user.getSecurities().getSecuritiesPk(), Types.NUMERIC);
		parameters.addValue("USER_NAME",  user.getUserName(), Types.VARCHAR);
		parameters.addValue("USER_PHONE_NO", user.getUserPhoneNo(), Types.VARCHAR);
		parameters.addValue("USER_EMAIL",  user.getUserEmail(), Types.VARCHAR);
		parameters.addValue("IS_ENABLED",  convertToInteger(user.getEnabled()), Types.NUMERIC);
		parameters.addValue("IS_LOCKED",  convertToInteger(user.getLocked()), Types.NUMERIC);
		parameters.addValue("CERTIFICATE",  user.getCertificateKey(), Types.VARCHAR);
		parameters.addValue("USER_NOTE",  user.getUserNote(), Types.VARCHAR);
		parameters.addValue("DEAL_SECURITIES_NOS",  user.getDealSecuritiesNos(), Types.VARCHAR);
		parameters.addValue("UPDATE_BY",  user.getUpdatedBy(), Types.VARCHAR);
		parameters.addValue("UPDATE_DATE",  user.getUpdatedDate(), Types.TIMESTAMP);
		parameters.addValue("CONFIRM_USER_PK",  user.getConfirmUserPk(), Types.NUMERIC);
		parameters.addValue("CONFIRM_STATUS",  user.getConfirmStatus(), Types.VARCHAR);
		parameters.addValue("CONFIRM_DATE",  user.getConfirmDate(), Types.TIMESTAMP);
		parameters.addValue("TEMP_USER_PK",  user.getTempUserPk(), Types.NUMERIC);
		parameters.addValue("GROUP_PK",  user.getGroup().getGroupPk(), Types.NUMERIC);
		
		return this.getSimpleJdbcTemplate().update(sql.toString(), parameters);
	}

	/*
	 * (non-Javadoc)
	 * @see com.feib.stms.dao.TempUserDao#getByTempUserId(java.lang.String, java.lang.Long)
	 */
	public TempUser getByTempUserId(String userId,Long groupPk) {
		StringBuffer sql = new StringBuffer();
		MapSqlParameterSource parameters = new MapSqlParameterSource();
		sql.append("SELECT U.*");
		sql.append(" FROM TEMP_USER U");
		sql.append(" WHERE 1 = 1 ");
		sql.append(" AND U.USER_ID = :USER_ID");
		sql.append(" AND U.CONFIRM_STATUS IS NULL");//放行狀態 null: 待放行 Y: 放行 N: 退回
		sql.append(" AND U.GROUP_PK = :GROUP_PK");
		parameters.addValue("USER_ID",  userId, Types.VARCHAR);
		parameters.addValue("GROUP_PK",  groupPk, Types.NUMERIC);
		
		return DataAccessUtils.singleResult(this.getSimpleJdbcTemplate().query(sql.toString(), new TempUserRowMapper(), parameters));
	}

	/*
	 * (non-Javadoc)
	 * @see com.feib.stms.dao.TempUserDao#queryForPaginatedListByTempUser(com.feib.stms.model.TempUser, com.feib.stms.pagination.PaginatedList)
	 */
	public PaginatedList<TempUser> queryForPaginatedListByTempUser(TempUser tempUser, PaginatedList paginatedList) {
		SqlQueryProvider sqlQueryProvider = getSqlQueryProviderByTempUser(tempUser, paginatedList);
		return this.queryForPaginatedList(sqlQueryProvider, new TempUserRowMapper(), paginatedList.getPageNumber(), paginatedList.getPageSize());
	}
	
	/*
	 * 拼接分頁查詢sql
	 */
	protected SqlQueryProvider getSqlQueryProviderByTempUser(TempUser tempUser , PaginatedList paginatedList)
	{
		/* 查詢 SQL */
		StringBuffer sql = new StringBuffer();
		
		/* 查詢條件 */
		List<Object> args = new ArrayList<Object>();
		
		sql.append("SELECT U.*");
		sql.append(" FROM TEMP_USER U WHERE 1 = 1 ");
		/* 放行狀態  --只查詢待放行的*/
		sql.append(" AND U.CONFIRM_STATUS IS NULL");
		if (null != tempUser)
		{	
			/* 使用者ID */
			if(!StringUtils.isEmpty(tempUser.getUserId())){
				sql.append(" AND U.USER_ID = ?");
				args.add(tempUser.getUserId());
			}
			/* 姓名 */
			if(!StringUtils.isEmpty(tempUser.getUserName())){
				sql.append(" AND U.USER_NAME LIKE ?");
				args.add("%" + tempUser.getUserName() + "%");
			}
		}
		//非銀行端，則不能全查，只能查詢自己所屬Group
		if(!UserUtil.isBankGroup()){
			sql.append(" AND U.GROUP_PK = ?");
			args.add(UserUtil.getUserGroup().getGroupPk());
		}
		/* 自己不能放行自己新增修改的資料*/
			sql.append(" AND  nvl(U.UPDATE_BY,U.CREATED_BY) <> ? ");
			args.add(UserUtil.getUserGroup().getGroupNo()+"_"+UserUtil.getUserId());
		
		/* 排序 */
		if (null != paginatedList)
		{
			if (StringUtils.isNotEmpty(paginatedList.getSortCriterion()))
			{
				sql.append(" ORDER BY ").append(paginatedList.getSortCriterion()).append(" ").append( 2==paginatedList.getSortDirection() ? "ASC" : "DESC" );
			}			
		}
		SqlQueryProvider sqlQueryProvider = new SqlQueryProvider(sql.toString(), args);
		return sqlQueryProvider;
	}
	
	/*
	 * (non-Javadoc)
	 * @see com.feib.stms.dao.TempUserDao#getTempUserCount(java.lang.String, java.lang.String, java.lang.Long)
	 */
	public int getTempUserCount(String userId, String password , Long groupPk) {
		/* 查詢 SQL */
		StringBuffer sql = new StringBuffer();
		/* 查詢條件 */
		MapSqlParameterSource parameters = new MapSqlParameterSource();
		sql.append("SELECT  COUNT(*)  FROM TEMP_USER WHERE USER_ID = :USER_ID AND USER_PWD = :USER_PWD");
		sql.append(" AND GROUP_PK = :GROUP_PK");
		parameters.addValue("USER_ID",  userId, Types.VARCHAR);
		parameters.addValue("USER_PWD",  password, Types.VARCHAR);
		parameters.addValue("GROUP_PK",  groupPk, Types.NUMERIC);
		int groupCount = this.getSimpleJdbcTemplate().queryForInt(sql.toString(), parameters);
		return groupCount;
	}
}
